 
  
GO

/****** Object:  StoredProcedure [dbo].[sprocEmployeeProfileTableSelectList]    Script Date: 07/05/2012 23:54:58 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[sprocEmployeeProfileTableSelectList]
@SchoolKey int
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	SELECT
		E.[EmployeeProfileKey],
		E.[UserAccountKey],
		E.[FirstName],
		E.[LastName],
		E.[OtherName],
		E.[Photo],
		E.[DateOfBirth],
		E.[PlaceOfBirth],
		E.[JoinDate],
		E.[ParentStatus],
		E.[Hobby],
		E.[BloodGroupCode],
		E.[MartialStatusCode],
		E.[RaceCode],
		E.[ReligionCode],
		E.[NationalityCode],
		E.[NRICNumber],
		E.[DrivingLicenceType],
		E.[DrivingLicenseNumber],
		E.[TravelDocumentType],
		E.[TravelDocumentNumber],
		E.[WebSiteUrl],
		E.[Status],
		E.[CreatedDateTime],
		E.[ModifiedDateTime],		 
		E.[Gender],
		E. FirstName + ' ' + E.LastName AS Employee,
		U.SchoolKey	
	FROM [EmployeeProfileTable] E
	INNER JOIN [UserAccountTable] AS U ON E.UserAccountKey = U.UserAccountKey
	WHERE U.SchoolKey = @SchoolKey AND u.UserAccountTypeCode = 800
	SET @Err = @@Error

	RETURN @Err
END

GO

/****** Object:  StoredProcedure [dbo].[sprocEmployeeProfileTableSelectListByBloodGroupCode]    Script Date: 07/05/2012 23:54:58 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[sprocEmployeeProfileTableSelectListByBloodGroupCode]
	@BloodGroupCode smallint,
	@SchoolKey int
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	SELECT
		E.[EmployeeProfileKey],
		E.[UserAccountKey],
		E.[FirstName],
		E.[LastName],
		E.[OtherName],
		E.[Photo],
		E.[DateOfBirth],
		E.[PlaceOfBirth],
		E.[JoinDate],
		E.[ParentStatus],
		E.[Hobby],
		E.[BloodGroupCode],
		E.[MartialStatusCode],
		E.[RaceCode],
		E.[ReligionCode],
		E.[NationalityCode],
		E.[NRICNumber],
		E.[DrivingLicenceType],
		E.[DrivingLicenseNumber],
		E.[TravelDocumentType],
		E.[TravelDocumentNumber],
		E.[WebSiteUrl],
		E.[Status],
		E.[CreatedDateTime],
		E.[ModifiedDateTime],		
		E.[Gender],
		E. FirstName + ' ' + E.LastName AS Employee,
		U.SchoolKey	
	FROM [EmployeeProfileTable] E
	INNER JOIN [UserAccountTable] AS U ON E.UserAccountKey = U.UserAccountKey
	WHERE U.SchoolKey = @SchoolKey AND u.UserAccountTypeCode = 800
	AND([BloodGroupCode] = @BloodGroupCode)
 

	SET @Err = @@Error

	RETURN @Err
END

GO

/****** Object:  StoredProcedure [dbo].[sprocEmployeeProfileTableSelectListByMartialStatusCode]    Script Date: 07/05/2012 23:54:58 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[sprocEmployeeProfileTableSelectListByMartialStatusCode]
	@MartialStatusCode smallint,
	@SchoolKey int
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	SELECT
		E.[EmployeeProfileKey],
		E.[UserAccountKey],
		E.[FirstName],
		E.[LastName],
		E.[OtherName],
		E.[Photo],
		E.[DateOfBirth],
		E.[PlaceOfBirth],
		E.[JoinDate],
		E.[ParentStatus],
		E.[Hobby],
		E.[BloodGroupCode],
		E.[MartialStatusCode],
		E.[RaceCode],
		E.[ReligionCode],
		E.[NationalityCode],
		E.[NRICNumber],
		E.[DrivingLicenceType],
		E.[DrivingLicenseNumber],
		E.[TravelDocumentType],
		E.[TravelDocumentNumber],
		E.[WebSiteUrl],
		E.[Status],
		E.[CreatedDateTime],
		E.[ModifiedDateTime],		
		E.[Gender],
		E. FirstName + ' ' + E.LastName AS Employee,
		U.SchoolKey	
	FROM [EmployeeProfileTable] E
	INNER JOIN [UserAccountTable] AS U ON E.UserAccountKey = U.UserAccountKey
	WHERE U.SchoolKey = @SchoolKey AND u.UserAccountTypeCode = 800	
	AND([MartialStatusCode] = @MartialStatusCode)

	SET @Err = @@Error

	RETURN @Err
END

GO

/****** Object:  StoredProcedure [dbo].[sprocEmployeeProfileTableSelectListByNationalityCode]    Script Date: 07/05/2012 23:54:58 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[sprocEmployeeProfileTableSelectListByNationalityCode]
	@NationalityCode smallint,
	@SchoolKey int
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	SELECT
		E.[EmployeeProfileKey],
		E.[UserAccountKey],
		E.[FirstName],
		E.[LastName],
		E.[OtherName],
		E.[Photo],
		E.[DateOfBirth],
		E.[PlaceOfBirth],
		E.[JoinDate],
		E.[ParentStatus],
		E.[Hobby],
		E.[BloodGroupCode],
		E.[MartialStatusCode],
		E.[RaceCode],
		E.[ReligionCode],
		E.[NationalityCode],
		E.[NRICNumber],
		E.[DrivingLicenceType],
		E.[DrivingLicenseNumber],
		E.[TravelDocumentType],
		E.[TravelDocumentNumber],
		E.[WebSiteUrl],
		E.[Status],
		E.[CreatedDateTime],
		E.[ModifiedDateTime],		
		E.[Gender],
		E. FirstName + ' ' + E.LastName AS Employee,
		U.SchoolKey	
	FROM [EmployeeProfileTable] E
	INNER JOIN [UserAccountTable] AS U ON E.UserAccountKey = U.UserAccountKey
	WHERE U.SchoolKey = @SchoolKey AND u.UserAccountTypeCode = 800	
	AND  ([NationalityCode] = @NationalityCode)

	SET @Err = @@Error

	RETURN @Err
END

GO

/****** Object:  StoredProcedure [dbo].[sprocEmployeeProfileTableSelectListByRaceCode]    Script Date: 07/05/2012 23:54:58 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[sprocEmployeeProfileTableSelectListByRaceCode]
	@RaceCode smallint,
	@SchoolKey int
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	SELECT
		E.[EmployeeProfileKey],
		E.[UserAccountKey],
		E.[FirstName],
		E.[LastName],
		E.[OtherName],
		E.[Photo],
		E.[DateOfBirth],
		E.[PlaceOfBirth],
		E.[JoinDate],
		E.[ParentStatus],
		E.[Hobby],
		E.[BloodGroupCode],
		E.[MartialStatusCode],
		E.[RaceCode],
		E.[ReligionCode],
		E.[NationalityCode],
		E.[NRICNumber],
		E.[DrivingLicenceType],
		E.[DrivingLicenseNumber],
		E.[TravelDocumentType],
		E.[TravelDocumentNumber],
		E.[WebSiteUrl],
		E.[Status],
		E.[CreatedDateTime],
		E.[ModifiedDateTime],		
		E.[Gender],
		E. FirstName + ' ' + E.LastName AS Employee,
		U.SchoolKey	
	FROM [EmployeeProfileTable] E
	INNER JOIN [UserAccountTable] AS U ON E.UserAccountKey = U.UserAccountKey
	WHERE U.SchoolKey = @SchoolKey AND u.UserAccountTypeCode = 800	 
	AND ([RaceCode] = @RaceCode)

	SET @Err = @@Error

	RETURN @Err
END

GO

/****** Object:  StoredProcedure [dbo].[sprocEmployeeProfileTableSelectListByReligionCode]    Script Date: 07/05/2012 23:54:58 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[sprocEmployeeProfileTableSelectListByReligionCode]
	@ReligionCode smallint,
	@SchoolKey int
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	SELECT
		E.[EmployeeProfileKey],
		E.[UserAccountKey],
		E.[FirstName],
		E.[LastName],
		E.[OtherName],
		E.[Photo],
		E.[DateOfBirth],
		E.[PlaceOfBirth],
		E.[JoinDate],
		E.[ParentStatus],
		E.[Hobby],
		E.[BloodGroupCode],
		E.[MartialStatusCode],
		E.[RaceCode],
		E.[ReligionCode],
		E.[NationalityCode],
		E.[NRICNumber],
		E.[DrivingLicenceType],
		E.[DrivingLicenseNumber],
		E.[TravelDocumentType],
		E.[TravelDocumentNumber],
		E.[WebSiteUrl],
		E.[Status],
		E.[CreatedDateTime],
		E.[ModifiedDateTime],		
		E.[Gender],
		E. FirstName + ' ' + E.LastName AS Employee,
		U.SchoolKey	
	FROM [EmployeeProfileTable] E
	INNER JOIN [UserAccountTable] AS U ON E.UserAccountKey = U.UserAccountKey
	WHERE U.SchoolKey = @SchoolKey AND u.UserAccountTypeCode = 800	
	AND ([ReligionCode] = @ReligionCode)
	SET @Err = @@Error

	RETURN @Err
END

GO

/****** Object:  StoredProcedure [dbo].[sprocEmployeeProfileTableSelectListByUserAccountKey]    Script Date: 07/05/2012 23:54:58 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[sprocEmployeeProfileTableSelectListByUserAccountKey]
	@UserAccountKey int,
	@SchoolKey int
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	SELECT
		E.[EmployeeProfileKey],
		E.[UserAccountKey],
		E.[FirstName],
		E.[LastName],
		E.[OtherName],
		E.[Photo],
		E.[DateOfBirth],
		E.[PlaceOfBirth],
		E.[JoinDate],
		E.[ParentStatus],
		E.[Hobby],
		E.[BloodGroupCode],
		E.[MartialStatusCode],
		E.[RaceCode],
		E.[ReligionCode],
		E.[NationalityCode],
		E.[NRICNumber],
		E.[DrivingLicenceType],
		E.[DrivingLicenseNumber],
		E.[TravelDocumentType],
		E.[TravelDocumentNumber],
		E.[WebSiteUrl],
		E.[Status],
		E.[CreatedDateTime],
		E.[ModifiedDateTime],		
		E.[Gender],
		E. FirstName + ' ' + E.LastName AS Employee,
		U.SchoolKey	
	FROM [EmployeeProfileTable] E
	INNER JOIN [UserAccountTable] AS U ON E.UserAccountKey = U.UserAccountKey
	WHERE U.SchoolKey = @SchoolKey AND u.UserAccountTypeCode = 800	
	AND (E.[UserAccountKey] = @UserAccountKey)
	SET @Err = @@Error

	RETURN @Err
END

 
GO

/****** Object:  StoredProcedure [dbo].[sprocEmployeeProfileTableSelectSingleItem]    Script Date: 07/05/2012 23:54:58 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[sprocEmployeeProfileTableSelectSingleItem]
(
	@EmployeeProfileKey int,
	@SchoolKey int
)
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @Err int

	SELECT
		E.[EmployeeProfileKey],
		E.[UserAccountKey],
		E.[FirstName],
		E.[LastName],
		E.[OtherName],
		E.[Photo],
		E.[DateOfBirth],
		E.[PlaceOfBirth],
		E.[JoinDate],
		E.[ParentStatus],
		E.[Hobby],
		E.[BloodGroupCode],
		E.[MartialStatusCode],
		E.[RaceCode],
		E.[ReligionCode],
		E.[NationalityCode],
		E.[NRICNumber],
		E.[DrivingLicenceType],
		E.[DrivingLicenseNumber],
		E.[TravelDocumentType],
		E.[TravelDocumentNumber],
		E.[WebSiteUrl],
		E.[Status],
		E.[CreatedDateTime],
		E.[ModifiedDateTime],		
		E.[Gender],
		E.FirstName + ' ' + E.LastName AS Employee,
		U.SchoolKey	
	FROM [EmployeeProfileTable] E
	INNER JOIN [UserAccountTable] AS U ON E.UserAccountKey = U.UserAccountKey
	WHERE U.SchoolKey = @SchoolKey AND u.UserAccountTypeCode = 800	 
    AND (E.[EmployeeProfileKey] = @EmployeeProfileKey)
	SET @Err = @@Error

	RETURN @Err
END

GO

/****** Object:  StoredProcedure [dbo].[sprocEmployeeProfileTableSelectSingleItemByUserAccountKey]    Script Date: 07/05/2012 23:54:58 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



ALTER PROCEDURE [dbo].[sprocEmployeeProfileTableSelectSingleItemByUserAccountKey]
(
	@UserAccountKey int,
	@SchoolKey int
)
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @Err int

	SELECT
		E.[EmployeeProfileKey],
		E.[UserAccountKey],
		E.[FirstName],
		E.[LastName],
		E.[OtherName],
		E.[Photo],
		E.[DateOfBirth],
		E.[PlaceOfBirth],
		E.[JoinDate],
		E.[ParentStatus],
		E.[Hobby],
		E.[BloodGroupCode],
		E.[MartialStatusCode],
		E.[RaceCode],
		E.[ReligionCode],
		E.[NationalityCode],
		E.[NRICNumber],
		E.[DrivingLicenceType],
		E.[DrivingLicenseNumber],
		E.[TravelDocumentType],
		E.[TravelDocumentNumber],
		E.[WebSiteUrl],
		E.[Status],
		E.[CreatedDateTime],
		E.[ModifiedDateTime],		
		E.[Gender],
		E.FirstName + ' ' + E.LastName AS Employee,
		U.SchoolKey	
	FROM [EmployeeProfileTable] E
	INNER JOIN [UserAccountTable] AS U ON E.UserAccountKey = U.UserAccountKey
	WHERE U.SchoolKey = @SchoolKey AND u.UserAccountTypeCode = 800	 
    AND (E.[UserAccountKey] = @UserAccountKey)
	SET @Err = @@Error

	RETURN @Err
END


 


